package DataBase;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Vector;
import java.sql.ResultSet;
import DataObjects.Bon;
import DataObjects.MetaDataOrder;

public class CloseTable {
private Vector<Bon> bones;
private MetaDataOrder metaDataOrder;
private int status=0;

public CloseTable(Vector<Bon> b, MetaDataOrder m)
{
	this.bones=b;
	this.metaDataOrder=m;
}

public void closeTable()
{
	
SQLConnection sc=new SQLConnection();
	
	try {
		sc.getConnection().setAutoCommit(false);
				
			PreparedStatement ps1 = sc.getConnection().prepareStatement("UPDATE  timeSlot SET dateClose=? ,timeClose=?  WHERE dateOpen =? AND timeOpen=?;");
			ps1.setString(1,(String) metaDataOrder.getDateClose());
			ps1.setString(2,(String) metaDataOrder.getTimeClose());
			ps1.setString(3,(String) metaDataOrder.getDate());
			ps1.setString(4,(String) metaDataOrder.getTime());	
			ps1.executeUpdate();
			
			PreparedStatement ps2 = sc.getConnection().prepareStatement("UPDATE table_timeslot SET status=?  WHERE dateOpen =? AND timeOpen=?;");
			ps2.setString(1,"closed");
			ps2.setString(2,(String) metaDataOrder.getDate());
			ps2.setString(3,(String) metaDataOrder.getTime());
			ps2.executeUpdate();			
			
			
			PreparedStatement ps3 = sc.getConnection().prepareStatement("SELECT * FROM menu BM WHERE BM.mid=? AND BM.mtype=?;");
			for(int i=0;i<bones.size();i++)
			{
			ps3.setString(1, bones.get(i).getMid());
			ps3.setString(2, "business");
			
			ResultSet rs3=ps3.executeQuery();
			
			
			if(rs3.next())          //if the bon is for business menu 
			{
				PreparedStatement ps4 = sc.getConnection().prepareStatement("SELECT IR.iid,IR.iQuantity FROM recipe_in_menu RBM,item_in_recipe IR WHERE RBM.mid=? AND RBM.isSelectable='0' AND RBM.rid=IR.rid;");
				PreparedStatement ps6 = sc.getConnection().prepareStatement("SELECT IR.iid,IR.iQuantity FROM recipe_in_menu RBM,item_in_recipe IR WHERE RBM.mid=? AND RBM.rid=? AND RBM.rid=IR.rid;");
				ps4.setString(1, bones.get(i).getMid());
				ResultSet rs4=ps4.executeQuery();
				//Vector<String> ridQuantity=new Vector<String>();
				Vector<Vector<String>> ridQuantityVec=new Vector<Vector<String>>();
				while(rs4.next())
				{
					Vector<String> ridQuantity=new Vector<String>();
					ridQuantity.add(rs4.getString(1));
					ridQuantity.add(rs4.getString(2));
					ridQuantityVec.add(ridQuantity);
					
				}
				
				ps6.setString(1, bones.get(i).getMid());
				ps6.setString(2, bones.get(i).getRid());
				ResultSet rs6=ps6.executeQuery();
				while(rs6.next())
				{
					Vector<String> ridQuantity=new Vector<String>();
					ridQuantity.add(rs6.getString(1));
					ridQuantity.add(rs6.getString(2));
					ridQuantityVec.add(ridQuantity);
					
				}
				PreparedStatement ps5 = sc.getConnection().prepareStatement("UPDATE Item SET iStoreQuantity=iStoreQuantity-?  WHERE iid =?;");
				for(int j=0;j<ridQuantityVec.size();j++)
				{
					ps5.setString(1,ridQuantityVec.get(j).get(1) );
					ps5.setString(2,ridQuantityVec.get(j).get(0) );
					ps5.executeUpdate();
				}
				
				ps4.close();
				rs4.close();
				ps6.close();
				rs6.close();
				ps5.close();
				
			} //if(rs3.next())
			
			
			rs3.close();
			
			}//for(int i=0;i<bones.size();i++)
			
					
			
			
			ps1.close();
			ps2.close();
			ps3.close();		
			status=1;
			
		sc.getConnection().commit();
		 sc.getConnection().close();
	
	}
	
	catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	}

public int getStatus() {
	return status;
}

public void setStatus(int status) {
	this.status = status;
}






}